from sqlalchemy.dialects.mysql import INTEGER, VARCHAR, TIMESTAMP
from sqlalchemy import Column, Date, DateTime
from sqlalchemy.orm import Session
from datetime import datetime, timezone, timedelta

from utils.extensions import db
from utils.tool import camelToSnake, timestampToDatetime


def addCommon(cls, data):
    # 创建一个新的记录实例
    new_record = cls()

    # 遍历传入的数据字典
    for key, value in data.items():
        snake_key = camelToSnake(key)
        # 检查该字段是否存在于模型中
        if hasattr(cls, snake_key):
            # 如果字段类型是DateTime或Date，转换时间戳
            if isinstance(getattr(cls, snake_key).type, (DateTime, Date)):
                value = timestampToDatetime(value / 1000)
            # 设置字段值
            setattr(new_record, snake_key, value)

    # 设置创建时间和最后修改时间为当前CST时间
    utc_now = datetime.now(timezone.utc)
    cst_now = utc_now + timedelta(hours=8)
    new_record.created_time = cst_now
    new_record.last_modified_at = cst_now

    return new_record


def updateCommon(new_data, record):

    for key, value in new_data.items():
        snake_key = camelToSnake(key)
        if hasattr(record, snake_key):
            if isinstance(getattr(record.__class__, snake_key).type, (DateTime, Date)):
                value = timestampToDatetime(value / 1000)
                print(value)
            setattr(record, snake_key, value)
    utc_now = datetime.now(timezone.utc)
    cst_now = utc_now + timedelta(hours=8)
    record.last_modified_at = cst_now


class Nav(db.Model):
    __tablename__ = 'nav_list'
    id = Column(INTEGER, primary_key=True)
    name = Column(VARCHAR(255), comment='菜单名称')
    parent_id = Column(INTEGER, default=0, comment='父菜单ID')
    path = Column(VARCHAR(255), comment='Path')
    sort = Column(INTEGER, default=0, comment='排序号')
    icon = Column(VARCHAR(255), comment='图标')
    status = Column(INTEGER, comment='启用状态')
    created_time = Column(TIMESTAMP)
    last_modified_at = Column(TIMESTAMP, nullable=True)  # 假设可以为NULL
    last_modified_by = Column(VARCHAR(255))

    def getList(self, status=None):
        result = {
            'id': self.id,
            'name': self.name,
            'parentId': self.parent_id,
            'path': self.path,
            'sort': self.sort,
            'icon': self.icon,
            'status': self.status,
            'createdTime': datetime.timestamp(self.created_time) * 1000,
            'lastModifiedAt': datetime.timestamp(self.last_modified_at) * 1000 if self.last_modified_at else None,
            'lastModifiedBy': self.last_modified_by,
        }
        if status is not None:
            if self.status == status:
                return result
            else:
                return None
        return result

    def add(cls, data, session: Session):
        # 创建一个新的记录实例
        new_record = addCommon(cls, data)

        # 添加记录到会话并提交
        session.add(new_record)
        session.commit()

        return new_record

    def update(cls, id, new_data, session: Session):
        record = session.query(cls).filter_by(id=id).first()

        if record is not None:
            updateCommon(new_data, record)
            session.commit()
        else:
            raise ValueError(f"找不到数据： {id}")

    def delete(cls, record_id, session: Session):
        # 查询指定ID的记录
        record = session.query(cls).get(record_id)

        # 检查记录是否存在
        if record:
            # 删除记录
            session.delete(record)
            # 提交会话以保存更改
            session.commit()
            return True
        else:
            # 记录不存在，返回False
            return False

class policyFiles(db.Model):
    __tablename__ = 'policy_files'
    id = Column(INTEGER, primary_key=True)
    file_name = Column(VARCHAR(255), comment='文件名称')
    area = Column(VARCHAR(255), comment='地区')
    major1 = Column(VARCHAR(255), comment='一级专业')
    major2 = Column(VARCHAR(255), comment='二级专业')
    url = Column(VARCHAR(255), comment='文件地址')
    status = Column(INTEGER, comment='启用状态')
    created_time = Column(TIMESTAMP)
    last_modified_at = Column(TIMESTAMP, nullable=True)
    last_modified_by = Column(VARCHAR(255))

    @classmethod
    def getList(cls, session: Session, area=None, major1=None, major2=None, keyword=None, page=1, per_page=10):
        query = session.query(cls)

        if area:
            query = query.filter(cls.area == area)
        if major1:
            query = query.filter(cls.major1 == major1)
        if major2:
            query = query.filter(cls.major2 == major2)
        if keyword:
            query = query.filter(cls.file_name.like(f'%{keyword}%'))

        total = query.filter(cls.status == 1).count()
        items = query.filter(cls.status == 1).offset((page - 1) * per_page).limit(per_page).all()
        
        return {
            'total': total,
            'list': [item.to_dict() for item in items],
            'page': page,
            'per_page': per_page
        }

    @classmethod
    def getPage(cls, session: Session, page=1, per_page=10, area=None, major1=None, major2=None, keyword=None):
        query = session.query(cls)

        if area:
            query = query.filter(cls.area == area)
        if major1:
            query = query.filter(cls.major1 == major1)
        if major2:
            query = query.filter(cls.major2 == major2)
        if keyword:
            query = query.filter(cls.file_name.like(f'%{keyword}%'))

        total = query.count()
        items = query.order_by(cls.id.desc()).offset((page - 1) * per_page).limit(per_page).all()

        return {
            'total': total,
            'list': [item.to_dict() for item in items],
            'page': page,
            'per_page': per_page
        }

    def to_dict(self):
        return {
            'id': self.id,
            'fileName': self.file_name,
            'area': self.area,
            'major1': self.major1,
            'major2': self.major2,
            'url': self.url,
            'status': self.status,
            'createdTime': datetime.timestamp(self.created_time) * 1000,
            'lastModifiedAt': datetime.timestamp(self.last_modified_at) * 1000 if self.last_modified_at else None,
            'lastModifiedBy': self.last_modified_by,
        }

    @classmethod
    def add(cls, data, session: Session):
        for file_item in data['fileList']:
            new_record = addCommon(cls, {
                'fileName': file_item['name'],
                'area': data['area'],
                'major1': data['major1'],
                'major2': data['major2'],
                'url': file_item['url'],
                'status': data['status']
            })
            session.add(new_record)
        
        session.commit()
        return new_record

    @classmethod
    def update(cls, id, new_data, session: Session):
        record = session.query(cls).filter_by(id=id).first()
        if record is not None:
            file_item = new_data['fileList'][0]  # 只处理第一项
            updateCommon({
                'fileName': file_item['name'],
                'area': new_data['area'],
                'major1': new_data['major1'],
                'major2': new_data['major2'],
                'url': file_item['url'],
                'status': new_data['status']
            }, record)
            session.commit()
            return True
        else:
            return False

    @classmethod
    def delete(cls, record_id, session: Session):
        record = session.query(cls).get(record_id)
        if record:
            session.delete(record)
            session.commit()
            return True
        return False
class BottomInfo(db.Model):
    id = Column(INTEGER, primary_key=True)
    guide_text = Column(VARCHAR(255), comment='引导文字')
    qr_code = Column(VARCHAR(255), comment='二维码')
    copyright_text = Column(VARCHAR(255), comment='版权文字')
    icp_number = Column(VARCHAR(50), comment='备案号')
    created_time = Column(TIMESTAMP)
    last_modified_at = Column(TIMESTAMP, nullable=True)
    last_modified_by = Column(VARCHAR(255), comment='最后修改人')

    @classmethod
    def get_by_id(cls, id, session: Session):
        return session.query(cls).filter_by(id=id).first()

    def to_dict(self):
        return {
            'id': self.id,
            'guideText': self.guide_text,
            'qrCode': self.qr_code,
            'copyrightText': self.copyright_text,
            'icpNumber': self.icp_number,
            'createdTime': datetime.timestamp(self.created_time) * 1000,
            'lastModifiedAt': datetime.timestamp(self.last_modified_at) * 1000 if self.last_modified_at else None,
            'lastModifiedBy': self.last_modified_by,
        }

    @classmethod
    def update(cls, id, new_data, session: Session):
        record = session.query(cls).filter_by(id=id).first()

        if record is not None:
            updateCommon(new_data, record)
            session.commit()
        else:
            raise ValueError(f"找不到数据： {id}")

class BackgroundImage(db.Model):
    __tablename__ = 'background_images'
    
    id = Column(INTEGER, primary_key=True)
    image_url = Column(VARCHAR(255), nullable=False, comment='图片URL')
    description = Column(VARCHAR(255), comment='图片描述')
    is_active = Column(INTEGER, default=0, comment='是否可在前台显示')
    sort_order = Column(INTEGER, default=0, comment='排序顺序')
    created_time = Column(TIMESTAMP, default=datetime.utcnow)
    last_modified_at = Column(TIMESTAMP, nullable=True)
    last_modified_by = Column(VARCHAR(255), comment='最后修改人')

    @classmethod
    def get_all_images(cls, session: Session):
        return session.query(cls).order_by(cls.sort_order).all()

    @classmethod
    def get_active_images(cls, session: Session):
        return session.query(cls).filter(cls.is_active == 1).order_by(cls.sort_order).all()

    @classmethod
    def add(cls, data, session: Session):
        new_data = {camelToSnake(k): v for k, v in data.items()}
        new_image = addCommon(cls, new_data)
        session.add(new_image)
        session.commit()
        return new_image

    @classmethod
    def update(cls, id, new_data, session: Session):
        image = session.query(cls).filter_by(id=id).first()
        if image:
            snake_data = {camelToSnake(k): v for k, v in new_data.items()}
            for key, value in snake_data.items():
                if isinstance(getattr(cls, key).type, (DateTime, Date)) and value is not None:
                    value = timestampToDatetime(value / 1000)
                setattr(image, key, value)
            utc_now = datetime.now(timezone.utc)
            cst_now = utc_now + timedelta(hours=8)
            image.last_modified_at = cst_now
            session.commit()
        else:
            raise ValueError(f"找不到背景图片： {id}")

    @classmethod
    def delete(cls, record_id, session: Session):
        record = session.query(cls).get(record_id)
        if record:
            session.delete(record)
            session.commit()
            return True
        return False

    @classmethod
    def set_active(cls, id, is_active, session: Session):
        image = session.query(cls).filter_by(id=id).first()
        if image:
            image.is_active = 1 if is_active else 0
            utc_now = datetime.now(timezone.utc)
            cst_now = utc_now + timedelta(hours=8)
            image.last_modified_at = cst_now
            session.commit()
            return True
        return False

    def to_dict(self):
        return {
            'id': self.id,
            'imageUrl': self.image_url,
            'description': self.description,
            'isActive': self.is_active,
            'sortOrder': self.sort_order,
            'createdTime': datetime.timestamp(self.created_time) * 1000,
            'lastModifiedAt': datetime.timestamp(self.last_modified_at) * 1000 if self.last_modified_at else None,
            'lastModifiedBy': self.last_modified_by,
        }

